package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.DateUtil;
import com.tianqu.tms.core.util.Registry;
import com.tianqu.tms.core.util.StringUtil;
import com.tianqu.tms.dto.FinanceDetailDto;
import com.tianqu.tms.dto.TransferFinanceDetailDto;
import com.tianqu.tms.entity.Codes;
import com.tianqu.tms.entity.TransferSettlementInfo;

public class TmsTransferFinanceDetailDaoImp extends TmsDao implements TmsTransferFinanceDetailDao {

	protected TmsTransferFinanceDetailDaoImp() throws TmsDaoException {
		super();
	}
	
	public List<TransferFinanceDetailDto> findTransferFinanceDetails(String serial,String priceWay,String priceType) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<TransferFinanceDetailDto> list = new ArrayList<TransferFinanceDetailDto>();

		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(" ID, ");
		sql.append(" PRICE, ");
		sql.append(" PRICECHARGETYPE, ");
		sql.append(" PRICEDATE, ");
		sql.append(" PRICEREMARK, ");
		sql.append(" PRICETYPE, ");
		sql.append(" PRICEUSERID, ");
		sql.append(" PRICEWAY, ");
		sql.append(" SERIAL ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL);
		sql.append(" WHERE SERIAL = '").append(serial).append("'");
		sql.append(" AND PRICEWAY = '").append(priceWay).append("'");
		sql.append(" AND PRICETYPE = '").append(priceType).append("'");
		String s = null;
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			Codes code = Codes.getInstance();
			while (rs.next()) {
				TransferFinanceDetailDto dto = new TransferFinanceDetailDto();
				dto.setId(rs.getInt("ID"));
				dto.setPriceStr(StringUtil.addDH(String.valueOf(rs.getInt("PRICE"))));
				dto.setPrice(rs.getInt("PRICE"));
				dto.setPriceChargeType(rs.getInt("PRICECHARGETYPE"));
				dto.setPriceDate(StringUtil.isNotBlank(rs.getString("PRICEDATE"))?DateUtil.getDateByTimeStampHM(rs.getTimestamp("PRICEDATE")):"");
				dto.setPriceRemark(rs.getString("PRICEREMARK"));
				dto.setPriceType(rs.getInt("PRICETYPE"));
				dto.setPriceTypeStr(code.getItemValueByItemIdAndItemCode(Codes.CHARGE_TYPE_CHARGE,rs.getInt("PRICETYPE")));
				dto.setPriceUserId(rs.getInt("PRICEUSERID"));
				dto.setPriceWay(rs.getInt("PRICEWAY"));
				dto.setSerial(rs.getString("SERIAL"));
				list.add(dto);
			}
			return list;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public List<TransferFinanceDetailDto> findTransferFinanceDetails(String serial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<TransferFinanceDetailDto> list = new ArrayList<TransferFinanceDetailDto>();

		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("A.ID, ");
		sql.append("A.PRICE, ");
		sql.append("A.PRICECHARGETYPE, ");
		sql.append("A.PRICEDATE, ");
		sql.append("A.PRICEREMARK, ");
		sql.append("A.PRICETYPE, ");
		sql.append("A.PRICEUSERID, ");
		sql.append("A.PRICEWAY, ");
		sql.append("A.SERIAL, ");
		sql.append("B.REALNAME ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL).append(" AS A");
		sql.append(" LEFT JOIN "+TmsTableNames.TMS_USER+" AS B ON (A.PRICEUSERID = B.ID)");
		sql.append(" WHERE SERIAL = '");
		sql.append(serial);
		sql.append("' ORDER BY PRICEDATE DESC");
		String s = null;
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			Codes code = Codes.getInstance();
			while (rs.next()) {
				TransferFinanceDetailDto dto = new TransferFinanceDetailDto();
				dto.setId(rs.getInt("ID"));
				dto.setPriceStr(StringUtil.addDH(String.valueOf(rs.getInt("PRICE"))));
				dto.setPrice(rs.getInt("PRICE"));
				dto.setPriceChargeType(rs.getInt("PRICECHARGETYPE"));
				dto.setPriceDate(StringUtil.isNotBlank(rs.getString("PRICEDATE"))?DateUtil.getDateByTimeStampHM(rs.getTimestamp("PRICEDATE")):"");
				dto.setPriceRemark(rs.getString("PRICEREMARK"));
				dto.setPriceType(rs.getInt("PRICETYPE"));
				dto.setPriceTypeStr(code.getItemValueByItemIdAndItemCode(Codes.CHARGE_TYPE_CHARGE,rs.getInt("PRICETYPE")));
				dto.setPriceUserId(rs.getInt("PRICEUSERID"));
				dto.setPriceWay(rs.getInt("PRICEWAY"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setPriceUserName(rs.getString("REALNAME"));
				list.add(dto);
			}
			return list;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public void insertTransferFinanceDetail(TransferFinanceDetailDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
	
		try {
			StringBuffer sql = new StringBuffer("INSERT INTO ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL);
			sql.append("(");
			sql.append(" SERIAL, ");
			sql.append(" PRICETYPE, ");
			sql.append(" PRICECHARGETYPE, ");
			sql.append(" PRICEWAY, ");
			sql.append(" PRICE, ");
			sql.append(" PRICEDATE, ");
			sql.append(" PRICEUSERID, ");
			sql.append(" PRICEREMARK ");
			sql.append(" ,acceptedtime ");
			sql.append("  ) VALUES ( ");
			sql.append(" ?,?,?,?,?,?,?,?, SYSDATE())");
			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, dto.getSerial());
			ps.setInt(2, dto.getPriceType());
			ps.setInt(3, dto.getPriceChargeType());
			ps.setInt(4, dto.getPriceWay());
			ps.setInt(5, dto.getPrice());
			ps.setString(6, dto.getPriceDate());
			ps.setInt(7, dto.getPriceUserId());
			ps.setString(8, dto.getPriceRemark());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	@Override
	public void insertTransferFinanceDetail102(TransferFinanceDetailDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
	
		try {
			StringBuffer sql = new StringBuffer("INSERT INTO ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL);
			sql.append("(");
			sql.append(" SERIAL, ");
			sql.append(" PRICETYPE, ");
			sql.append(" PRICECHARGETYPE, ");
			sql.append(" PRICE, ");
			sql.append(" PRICEDATE, ");
			sql.append(" PRICEUSERID, ");
			sql.append(" PRICEREMARK ");
			sql.append("  ) VALUES ( ");
			sql.append(" ?,?,?,?,?,?,? )");
			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, dto.getSerial());
			ps.setInt(2, dto.getPriceType());
			ps.setInt(3, dto.getPriceChargeType());
			ps.setInt(4, dto.getPrice());
			ps.setString(5, dto.getPriceDate());
			ps.setInt(6, dto.getPriceUserId());
			ps.setString(7, dto.getPriceRemark());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	

    @Override
    public void insertFinanceDetail(FinanceDetailDto financeDetailDto) throws TmsDaoException {
            PreparedStatement ps = null;
            ResultSet rs = null;
            
            try {
                StringBuffer sql = new StringBuffer("INSERT INTO ");
                sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL);
                sql.append("(");
                sql.append("serial,");
                sql.append("priceType,");
                sql.append("priceChargeType,");
                sql.append("priceWay,");
                sql.append("price,");
                sql.append("priceDate,");
                sql.append("priceUserId");
                sql.append("  ) VALUES ( ");
                sql.append("?,?,?,?,?,SYSDATE(),?)");
                ps = conn.prepareStatement(sql.toString());
                
                ps.setString(1, financeDetailDto.getSerial());
                ps.setInt(2, financeDetailDto.getPriceType());
                ps.setInt(3, financeDetailDto.getPriceChangeType());
                ps.setInt(4, financeDetailDto.getPriceWay());
                ps.setInt(5, financeDetailDto.getPrice());
                ps.setInt(6, financeDetailDto.getPriceUserId());
                
                ps.executeUpdate();
            } catch (SQLException e) {
                throw new TmsDaoException(e.toString());
            } finally {
                close(rs, ps);
            }
    }
    
    @Override
    public void updateFinanceDetailAcceptedTime(String serial) throws TmsDaoException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            StringBuffer sql = new StringBuffer("update ");
            sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL);
            sql.append(" set acceptedTime=SYSDATE() where priceType=9 and serial=?");
            ps = conn.prepareStatement(sql.toString());
            ps.setString(1, serial);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new TmsDaoException(e.toString());
        } finally {
            close(rs, ps);
        }
    }
    
	@Override
	public List<TransferSettlementInfo> getFinaneInfoByPeriod(String startDate, String endDate,int userid, int settleId, int itemId) throws TmsDaoException {
		
		//应收金额（receivable）
		PreparedStatement ps1 = null;
		ResultSet rs1 = null;
		Map<Integer, Integer> m1 = new HashMap<Integer, Integer>();
		StringBuffer sql1 = new StringBuffer("");
		sql1.append("select  A.itemCode priceType, ifnull(B.count, 0) count from tms_codes A left join (");
		sql1.append("SELECT ");
		sql1.append(" priceType,");
		sql1.append(" priceWay,");
		sql1.append(" COUNT(*) AS count ");
		sql1.append(" FROM ");
		sql1.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL);
		sql1.append(" WHERE DATE_FORMAT(priceDate,'%Y-%m-%d %H:%i')>'"+startDate+"' AND DATE_FORMAT(priceDate,'%Y-%m-%d %H:%i')<='"+endDate+"' ");
		sql1.append(" AND priceUserId="+userid+"  and priceWay = 1 ");
		sql1.append(" GROUP BY priceType) B on A.itemCode = B.priceType where A.itemId=").append(itemId);
		try {
			ps1 = conn.prepareStatement(sql1.toString());
			rs1 = ps1.executeQuery();
			while (rs1.next()) {
				int priceType = rs1.getInt("priceType");
				int count = rs1.getInt("count");
				int price = Integer.parseInt(Registry.getConfig("priceType"+priceType));
				m1.put(priceType, price * count);
			}
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs1, ps1);
		}
		
		//应退金额（receivableBack）
		PreparedStatement ps2 = null;
		ResultSet rs2 = null;
		Map<Integer, Integer> m2 = new HashMap<Integer, Integer>();
		StringBuffer sql2 = new StringBuffer("");
		sql2.append("select  A.itemCode priceType, ifnull(B.count, 0) count from tms_codes A left join (");
		sql2.append("SELECT ");
		sql2.append(" priceType,");
		sql2.append(" priceWay,");
		sql2.append(" COUNT(*) AS count ");
		sql2.append(" FROM ");
		sql2.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL);
		sql2.append(" WHERE DATE_FORMAT(priceDate,'%Y-%m-%d %H:%i')>'"+startDate+"' AND DATE_FORMAT(priceDate,'%Y-%m-%d %H:%i')<='"+endDate+"' ");
		sql2.append(" AND priceUserId="+userid+"  and priceWay = 2 ");
		sql2.append(" GROUP BY priceType) B on A.itemCode = B.priceType where A.itemId=").append(itemId);
		try {
			ps2 = conn.prepareStatement(sql2.toString());
			rs2 = ps2.executeQuery();
			while (rs2.next()) {
				int priceType = rs2.getInt("priceType");
				int count = rs2.getInt("count");
				int price = Integer.parseInt(Registry.getConfig("priceType"+priceType));
				m2.put(priceType, -1 * price * count);
			}
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs2, ps2);
		}
		
		
		//实收金额
		PreparedStatement ps3 = null;
		ResultSet rs3 = null;
		Map<Integer, Integer> m3 = new HashMap<Integer, Integer>();
		StringBuffer sql3 = new StringBuffer("");
		sql3.append("select  A.itemCode priceType, ifnull(B.price, 0) price from tms_codes A left join (");
		sql3.append("SELECT ");
		sql3.append(" priceType,");
		sql3.append(" sum(price) price ");
		sql3.append(" FROM ");
		sql3.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL);
		sql3.append(" WHERE DATE_FORMAT(priceDate,'%Y-%m-%d %H:%i')>'"+startDate+"' AND DATE_FORMAT(priceDate,'%Y-%m-%d %H:%i')<='"+endDate+"' ");
		sql3.append(" AND priceUserId="+userid+" ");
		sql3.append(" and priceChargeType=1 and priceWay = 1");
		sql3.append(" GROUP BY priceType ) B on A.itemCode = B.priceType where A.itemId=").append(itemId);
		try {
			ps3 = conn.prepareStatement(sql3.toString());
			rs3 = ps3.executeQuery();
			while (rs3.next()) {
				int priceType = rs3.getInt("priceType");
				int price = rs3.getInt("price");
				m3.put(priceType, price);
			}
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs3, ps3);
		}
		
		//实退金额
		PreparedStatement ps4 = null;
		ResultSet rs4 = null;
		Map<Integer, Integer> m4 = new HashMap<Integer, Integer>();
		StringBuffer sql4 = new StringBuffer("");
		sql4.append("select  A.itemCode priceType, ifnull(B.price, 0) price from tms_codes A left join (");
		sql4.append("SELECT ");
		sql4.append(" priceType,");
		sql4.append(" sum(price) price ");
		sql4.append(" FROM ");
		sql4.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL);
		sql4.append(" WHERE DATE_FORMAT(priceDate,'%Y-%m-%d %H:%i')>'"+startDate+"' AND DATE_FORMAT(priceDate,'%Y-%m-%d %H:%i')<='"+endDate+"' ");
		sql4.append(" AND priceUserId="+userid+" ");
		sql4.append(" and priceChargeType=1 and priceWay = 2");
		sql4.append(" GROUP BY priceType ) B on A.itemCode = B.priceType where A.itemId=").append(itemId);
		try {
			ps4 = conn.prepareStatement(sql4.toString());
			rs4 = ps4.executeQuery();
			while (rs4.next()) {
				int priceType = rs4.getInt("priceType");
				int price = rs4.getInt("price");
				m4.put(priceType, -1*price);
			}
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs4, ps4);
		}
		
		//
		List<TransferSettlementInfo> list = new ArrayList<TransferSettlementInfo>();
		Codes code = Codes.getInstance();
		List<Codes> types = code.getCodesListByItemId(Codes.CHARGE_TYPE_CHARGE);
		for (Codes codes : types) {
			TransferSettlementInfo info = new TransferSettlementInfo();
			info.setSettleId(settleId);
			info.setSettleUserId(userid);
			info.setReceivable(m1.get(codes.getItemCode()));
			info.setReceivableBack(m2.get(codes.getItemCode()));
			info.setPaid(m3.get(codes.getItemCode()));
			info.setPaidBack(m4.get(codes.getItemCode()));
			info.setInsertUserId(userid);
			info.setSettletype(codes.getItemCode());
			list.add(info);
		}
		return list;
	}
}
